/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package sgbd;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author julio
 */
public class ClienteDAO {

    private Connection cn;
    private static final String sqlInsert="insert into cliente(nome,endereco,cidade,email)values(?,?,?,?)";
    private static final String sqlSelect = "select * from cliente";
    private static final String sqlupdate = "update cliente set nome=?,endereco=?,cidade=?,email=? where codigo=?";
    private static final String sqlDelete = "delete from cliente where codigo=?;";
    
    
    public ClienteDAO() throws IOException, FileNotFoundException, ClassNotFoundException, SQLException {
        this.cn =  new ConnectionFactory().getConnection();
    }
    public void insertCliente(ClienteDTO cliente) throws SQLException{
        PreparedStatement stmt = cn.prepareStatement(sqlInsert);
        stmt.setString(1, cliente.getNome());
        stmt.setString(2, cliente.getEndereco());
        stmt.setString(3, cliente.getEmail());
        stmt.execute();
        stmt.close();
        cn.close();
    }
    public void deleteCliente(int codigo) throws SQLException{
       PreparedStatement stmt = cn.prepareStatement(sqlDelete);
       stmt.setInt(1, codigo);
       stmt.execute();
       stmt.close();
       cn.close();
    }
    public void updateCliente(ClienteDTO cliente) throws SQLException{
         PreparedStatement stmt = cn.prepareStatement(sqlupdate);
       stmt.setString(1, cliente.getNome());
       stmt.setString(2, cliente.getEndereco());
       stmt.setDouble(3, cliente.getCidade());
       stmt.setInt(4, cliente.getCodigo());
       stmt.execute();
       stmt.close();
       cn.close(); 
    }
    public List<ClienteDTO> selectCliente(ClienteDTO cliente) throws SQLException{
              List<ClienteDTO> lista = new ArrayList();
     PreparedStatement stmt = cn.prepareStatement(sqlSelect);
     ResultSet rs = stmt.executeQuery();
     while(rs.next()){
         ClienteDTO client = new ClienteDTO();
         client.setCodigo(rs.getInt("nome"));
         client.setNome(rs.getString("endereco"));
         client.setCidade(rs.getInt("cidade"));
         client.setEmail(rs.getString("email"));
        lista.add(client);
     }
     return lista;
    }
    
}
